﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CarLoanSystem.Entity;
using System.Data.SqlClient;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class DownPaymentInfoDAL : DBBase
    {
        public static DownPaymentInfoItem LoadDownPayment(string id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [DOWN_PAYMENT_ID]
                                                      ,[DOWN_PAYMENT_RATE]
                                                  FROM [CAR_LOAN].[dbo].[DOWN_PAYMENT_INFO]
                                                    where [DOWN_PAYMENT_ID] = @DOWN_PAYMENT_ID ", DBBase.con);
                command.Parameters.AddWithValue("@DOWN_PAYMENT_ID", id.ToLower());
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    DownPaymentInfoItem item = new DownPaymentInfoItem();
                    item.DOWN_PAYMENT_ID = Convert.ToInt32(dt.Rows[0]["DOWN_PAYMENT_ID"].ToString());
                    item.DOWN_PAYMENT_RATE = Convert.ToInt32(dt.Rows[0]["DOWN_PAYMENT_RATE"].ToString());
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllDownPayment(string id = "", string downRate = "")
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                string query = @"SELECT [DOWN_PAYMENT_ID] ,[DOWN_PAYMENT_RATE] FROM [CAR_LOAN].[dbo].[DOWN_PAYMENT_INFO] where 1=1";
                if (id != "") 
                {
                    query += " And DOWN_PAYMENT_ID = " + id;
                }
                if (downRate != "")
                {
                    query += " And DOWN_PAYMENT_RATE = " + downRate;
                }

                SqlCommand command = new SqlCommand(query, DBBase.con);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static bool Insert(DownPaymentInfoItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[DOWN_PAYMENT_INFO]
                                                       ([DOWN_PAYMENT_RATE])
                                                 VALUES
                                                      ( @DOWN_PAYMENT_RATE)", DBBase.con);

                command.Parameters.AddWithValue("@DOWN_PAYMENT_RATE", item.DOWN_PAYMENT_RATE);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool Update(DownPaymentInfoItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[DOWN_PAYMENT_INFO]
                                                    SET [DOWN_PAYMENT_RATE] = @DOWN_PAYMENT_RATE
                                             WHERE ([DOWN_PAYMENT_ID] = @DOWN_PAYMENT_ID)", DBBase.con);

                command.Parameters.AddWithValue("@DOWN_PAYMENT_ID", item.DOWN_PAYMENT_ID);
                command.Parameters.AddWithValue("@DOWN_PAYMENT_RATE", item.DOWN_PAYMENT_RATE);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }
    }
}
